package Z07JDBC.DB09DAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李某
 * * @date 2021/10/21
 * *物有本末，事有终始。知其先后者，则近道矣！
 * *荷花开满池塘的前一天还是半塘荷花半塘云。
 */
public class HeroDAO implements DAO {

    //驱动
    public HeroDAO() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // 初始化加载 MySQL 驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 连接到数据库
    public Connection getConnection() throws SQLException {
        return
                DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
                        "root");
    }


    public int getTotal() {
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {
            String sql = "select count(*) from hero";

            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            System.out.println("total :" + total);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }


    @Override
    public void add(Hero hero) {
        String sql = "insert into hero values(null,?,?,?)";
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql);) {


            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setInt(3, hero.damage);

            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                hero.id = id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void update(Hero hero) {
        String sql = "update hero set name = ?," +
                "hp = ?,damage = ? where id = ?";

        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql);){

            ps.setString(1,hero.name);
            ps.setFloat(2,hero.hp);
            ps.setInt(3,hero.damage);
            ps.setInt(4,hero.id);

            ps.execute();
        }catch (SQLException e ){
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {

        try (Connection c =  getConnection();Statement s = c.createStatement();){

            String aql = "delete from hero where id = " + id ;
            s.execute(aql);

        }catch (SQLException e){
            e.printStackTrace();
        }

    }

    @Override
    public Hero get(int id) {
        Hero hero = null;

        try(Connection c = getConnection();
            Statement s = c.createStatement();
        ){
            String sql = "select * from hero where id = " + id;
            ResultSet rs = s.executeQuery(sql);

            if (rs.next()){
                hero = new Hero();
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);

                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                hero.id = id;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return hero;
    }

    @Override
    public List<Hero> list() {
        return list(0,Short.MAX_VALUE);
    }

    @Override
    public List<Hero> list(int start, int count) {

        List<Hero> heros = new ArrayList<Hero>();

        String sql = "select * from hero order by id desc limit ?,?";
        try (Connection c = getConnection();PreparedStatement ps = c.prepareStatement(sql);){
            ps.setInt(1,start);
            ps.setInt(2,count);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                Hero hero = new Hero();
                int id = rs.getInt(1);
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.id = id;
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                heros.add(hero);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return heros;
    }
}
